package com.yuanjin.util;

import android.R.integer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class SQLiteHelper extends SQLiteOpenHelper {

	private final static String DATABASE_NAME = "Library";
	private final static int DATABASE_VERSION = 3;
	private final static String TABLE_NAME = "toxiaoqiang";

	private String CREATE_BOOK = "create table toxiaoqiang(_id integer primarykey,date INTEGER,datesecond VARCHAR(20),content TEXT);";
	private String CREATE_TEMP_BOOK = "alter table toxiaoqiang rename to _temp_book";
	private String INSERT_DATA = "insert into toxiaoqiang select *,'' from _temp_book";
	private String DROP_BOOK = "drop table _temp_book";

	// 构造函数，创建数据库
	public SQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		Log.e("新建数据库", "程序有执行SQLiteHelper了");
	}

	// 建表
	// _id：数据库中id号、TextView_ID：格子的id号、ClassName：课程名称、Place：课室、Teacher：老师名字、
	// Week上课周数、WeekDay：周x上课、FirstClass：首节课数、LastClass：尾节课数
	public void onCreate(SQLiteDatabase db) {
		// 写sql语句时别忘了逗号
		String sql = "CREATE TABLE " + TABLE_NAME + "(_id INTEGER PRIMARY KEY,"
				+ " datesecond INTEGER," + " date VARCHAR(20),"+ " username VARCHAR(15),"
				+ " content TEXT)";
		// + " ClassTime VARCHAR(20)," + "LastClass INT(3),"
		// + " ClassName VARCHAR(30)  NOT NULL," + " Place VARCHAR(10),"
		// + " Teacher VARCHAR(10), " + "FirstClass INT(3),"

		db.execSQL(sql);
		Log.e("新建数据库", "程序有执行了");
	}

	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;
		// db.execSQL(sql);
		// onCreate(db);
		switch (newVersion) {
		case 2:
			db.execSQL(CREATE_TEMP_BOOK);
			db.execSQL(CREATE_BOOK);
			db.execSQL(INSERT_DATA);
			db.execSQL(DROP_BOOK);
			break;

		}
	}

	// 获取游标
	public Cursor select() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db
				.query(TABLE_NAME, null, null, null, null, null, null);
		return cursor;
	}

	// 插入一条记录
	public long insert(String date, int datesecond, String content,String username) {

		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put("date", date);
		cv.put("datesecond", datesecond);
		cv.put("content", content);
		cv.put("username", username);
		long row = db.insert(TABLE_NAME, null, cv);
		return row;
	}

	// 根据条件查询
	public Cursor query(String[] args) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME
				+ " WHERE TextView_ID=? ", args);

		return cursor;
	}

	// 查询全部记录
	public Cursor queryAll() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);

		return cursor;
	}

	// 删除记录
	public void delete(String content) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = "content = ?";
		String[] whereValue = { content };
		db.delete(TABLE_NAME, where, whereValue);
	}
	
	// 删除所有记录
		public void deleteAll() {
			SQLiteDatabase db = this.getWritableDatabase();
//			String where = "content = ";
//			String[] whereValue = { "" };
//			db.delete(TABLE_NAME, where, whereValue);
			db.delete(TABLE_NAME,null,null);
//			db.execSQL("DELETE FROM toxiaoqiang");
		}

	// 更新记录
	public void update(String TextView_ID, String ClassName, String Place,
			String Teacher, String Week, int WeekDay, int FirstClass,
			int LastClass, String ClassTime) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = "TextView_ID = ?";
		String[] whereValue = { TextView_ID };
		ContentValues cv = new ContentValues();
		cv.put("ClassName", ClassName);
		cv.put("Place", Place);
		cv.put("Teacher", Teacher);
		cv.put("FirstClass", FirstClass);
		cv.put("LastClass", LastClass);
		cv.put("Week", Week);
		cv.put("ClassTime", ClassTime);
		cv.put("WeekDay", WeekDay);
		db.update(TABLE_NAME, cv, where, whereValue);
	}

	// 课表初始化时更新TextViewID记录,根据工作日和首节课时来索引
	public void updateID(String TextView_ID, int WeekDay, int FirstClass) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = "WeekDay = ? and FirstClass=?";
		String[] whereValue = { Integer.toString(WeekDay),
				Integer.toString(FirstClass) };
		ContentValues cv = new ContentValues();
		cv.put("TextView_ID", TextView_ID);
		db.update(TABLE_NAME, cv, where, whereValue);
	}

}